/**
 * 
 */
package org.framework2.utils;

import java.sql.Clob;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import oracle.sql.CLOB;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSet;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

/**
 * <p>
 * 数据库操作工具类，提供简单的数据库操作API
 * </p>
 * @see 
 * @author aohai.li
 * @version WebServerTestMockV1, 2010-8-11
 * @since WebServerTestMockV1
 */
public class DBTools {

	/**
	 * jdbc模板
	 */
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 事务模板
	 */
	private TransactionTemplate transactionTemplate;
	
	private DefaultLobHandler lobHandler;
	
	public DBTools(JdbcTemplate jdbcTemplate, PlatformTransactionManager transactionManager, DefaultLobHandler lobHandler){
		transactionTemplate = new TransactionTemplate(transactionManager);
		transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
		transactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
		transactionTemplate.setTimeout(30);
		
		this.jdbcTemplate = jdbcTemplate;
		this.lobHandler = lobHandler;
	}
	
	/**
	 * 执行更新操作
	 * @param sql
	 */
	public void update(final String sql){
		transactionTemplate.execute(new TransactionCallback<Object>() {
			@Override
			public Object doInTransaction(TransactionStatus arg0) {
				jdbcTemplate.update(sql);
				return null;
			}
		});
	}
	
	/**
	 * 执行查询操作
	 * @param sql
	 * @return
	 */
	public List<Map<String, Object>> select(final String sql){
		return transactionTemplate.execute(new TransactionCallback<List<Map<String, Object>>>() {
			@Override
			public List<Map<String, Object>> doInTransaction(TransactionStatus arg0) {
				return jdbcTemplate.queryForList(sql);
			}
		});
	}
	
	/**
	 * 执行查询操作
	 * @param sql
	 * @return
	 */
	public ResultSet queryForResultSet(final String sql){
		return transactionTemplate.execute(new TransactionCallback<ResultSet>(){
			@Override
			public ResultSet doInTransaction(TransactionStatus arg0)
			{
				ResultSetWrappingSqlRowSet temp = (ResultSetWrappingSqlRowSet)jdbcTemplate.queryForRowSet(sql);
				if(null != temp)
				{
					return temp.getResultSet();
				}
				else
				{
					return null;
				}
			}
		});
	}
	
	/**
	 * 
	 * @param caseid
	 * @param type    1:收到的请求报文；2:响应；3:回执
	 * @param message
	 */
	public void insertClobXML (final int caseid, final String type, final String message)
	{
		String temp = null;
		
		if(StringUtils.isBlank(message))
		{
			temp = "insert into cms_test_mock_log(caseid,type) values(?,?)";
		}
		else
		{
			temp = "insert into cms_test_mock_log(caseid,type,message) values(?,?,?)";
		}
		
		final String sql = temp;
		
		
		transactionTemplate.execute(new TransactionCallback<Object>() {
			@Override
			public Object doInTransaction(TransactionStatus arg0)
			{
				jdbcTemplate.execute(sql, new AbstractLobCreatingPreparedStatementCallback(new DefaultLobHandler())
				{
					@Override
					protected void setValues(PreparedStatement ps,
							LobCreator lobCreator) throws SQLException,
							DataAccessException
					{
						ps.setInt(1, caseid);
						ps.setString(2, type);
						if(!StringUtils.isBlank(message))
						{
							lobCreator.setClobAsString(ps, 3, message);
						}
					}
					
				});
				return null;
			}
			
		});
	}
	
	/**
	 * 获取clob字段
	 * @param rs
	 * @param columnName
	 * @return
	 */
	public String getClobMessage(ResultSet rs , String columnName)
	{
		try
		{
			Clob c = rs.getClob(columnName);
			if(c.length() <= 0L)
			{
				return "";
			}
			else
			{
				return c.getSubString(1L, (int)c.length()).trim(); 
			}
			
//			return lobHandler.getClobAsString(rs, columnName);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return "test mock DBTools.getClobMessage exception...";
		}
	}
	
	/**
	 * 获取String字段
	 * @param rs
	 * @param columnName
	 * @return
	 */
	public String getStringMessage(ResultSet rs , String columnName)
	{
		try
		{
			return rs.getString(columnName);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return "test mock DBTools.getStringMessage exception...";
		}
	}
	
	/**
	 * 获取int字段
	 * @param rs
	 * @param columnName
	 * @return
	 */
	public Integer getNumberMessage(ResultSet rs , String columnName)
	{
		try
		{
			return rs.getInt(columnName);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return null;
		}
	}
}
